package com.ymatou.query.seller.trading.api.parameter;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import com.ymttest.database.sqlwapper.DefaultWapper;
import com.ymttest.database.sqlwapper.YmtOrdersWapper;
import com.ymttest.utils.DataUtil;
import com.ymttest.utils.YMTDateUtil;

/**
 * 存储过程: sp_trd_GetSellerOrderList_v2
 * 
 * @author songyefeng
 *
 */
@SuppressWarnings("rawtypes")
public class Sp_GetSellerOrderList_v2 {
	private Integer sellerId;
	private Integer orderType;
	private Boolean shangou;
	private List<Integer> orderStatus;
	private List<Integer> catalogStatus;
	private String addBeginTime;
	private String addEndTime;
	private String paidBeginTime;
	private String paidEndTime;
	private Boolean paidInFull;
	private Integer rowFrom;
	private Integer rowTo;
	private String buyerNickName;
	private String productName;
	private Boolean considerOrderStatus;
	private Boolean considerRCOrderEstablish;
	private Boolean considerRCAccountPaid;
	private Boolean considerRestOrderStatus;
	private boolean salesRefundOrderOnly;
	private Boolean domesticDelivered;
	private Integer mainOrderId;
	private Integer OrderId;
	private Integer RemarkLevel;

	public Integer getSellerId() {
		return sellerId;
	}

	public void setSellerId(Integer sellerId) {
		this.sellerId = sellerId;
	}

	public Integer getOrderType() {
		return orderType;
	}

	public void setOrderType(Integer orderType) {
		this.orderType = orderType;
	}

	public Boolean getShangou() {
		return shangou;
	}

	public void setShangou(Boolean shangou) {
		this.shangou = shangou;
	}

	public List<Integer> getOrderStatus() {
		return orderStatus;
	}

	public void setOrderStatus(List<Integer> orderStatus) {
		this.orderStatus = orderStatus;
	}

	public List<Integer> getCatalogStatus() {
		return catalogStatus;
	}

	public void setCatalogStatus(List<Integer> catalogStatus) {
		this.catalogStatus = catalogStatus;
	}

	public String getAddBeginTime() {
		return addBeginTime;
	}

	public void setAddBeginTime(String addBeginTime) {
		this.addBeginTime = addBeginTime;
	}

	public String getAddEndTime() {
		return addEndTime;
	}

	public void setAddEndTime(String addEndTime) {
		this.addEndTime = addEndTime;
	}

	public String getPaidBeginTime() {
		return paidBeginTime;
	}

	public void setPaidBeginTime(String paidBeginTime) {
		this.paidBeginTime = paidBeginTime;
	}

	public String getPaidEndTime() {
		return paidEndTime;
	}

	public void setPaidEndTime(String paidEndTime) {
		this.paidEndTime = paidEndTime;
	}

	public Boolean getPaidInFull() {
		return paidInFull;
	}

	public void setPaidInFull(Boolean paidInFull) {
		this.paidInFull = paidInFull;
	}

	public Integer getRowFrom() {
		return rowFrom;
	}

	public void setRowFrom(Integer rowFrom) {
		this.rowFrom = rowFrom;
	}

	public Integer getRowTo() {
		return rowTo;
	}

	public void setRowTo(Integer rowTo) {
		this.rowTo = rowTo;
	}

	public String getBuyerNickName() {
		return buyerNickName;
	}

	public void setBuyerNickName(String buyerNickName) {
		this.buyerNickName = buyerNickName;
	}

	public String getProductName() {
		return productName;
	}

	public void setProductName(String productName) {
		this.productName = productName;
	}

	public Boolean getConsiderOrderStatus() {
		return considerOrderStatus;
	}

	public void setConsiderOrderStatus(Boolean considerOrderStatus) {
		this.considerOrderStatus = considerOrderStatus;
	}

	public Boolean getConsiderRCOrderEstablish() {
		return considerRCOrderEstablish;
	}

	public void setConsiderRCOrderEstablish(Boolean considerRCOrderEstablish) {
		this.considerRCOrderEstablish = considerRCOrderEstablish;
	}

	public Boolean getConsiderRCAccountPaid() {
		return considerRCAccountPaid;
	}

	public void setConsiderRCAccountPaid(Boolean considerRCAccountPaid) {
		this.considerRCAccountPaid = considerRCAccountPaid;
	}

	public Boolean getConsiderRestOrderStatus() {
		return considerRestOrderStatus;
	}

	public void setConsiderRestOrderStatus(Boolean considerRestOrderStatus) {
		this.considerRestOrderStatus = considerRestOrderStatus;
	}

	public boolean isSalesRefundOrderOnly() {
		return salesRefundOrderOnly;
	}

	public void setSalesRefundOrderOnly(boolean salesRefundOrderOnly) {
		this.salesRefundOrderOnly = salesRefundOrderOnly;
	}

	public Boolean getDomesticDelivered() {
		return domesticDelivered;
	}

	public void setDomesticDelivered(Boolean domesticDelivered) {
		this.domesticDelivered = domesticDelivered;
	}

	public Integer getMainOrderId() {
		return mainOrderId;
	}

	public void setMainOrderId(Integer mainOrderId) {
		this.mainOrderId = mainOrderId;
	}

	public Integer getOrderId() {
		return OrderId;
	}

	public void setOrderId(Integer orderId) {
		OrderId = orderId;
	}

	public Integer getRemarkLevel() {
		return RemarkLevel;
	}

	public void setRemarkLevel(Integer remarkLevel) {
		RemarkLevel = remarkLevel;
	}

	private class MainOrderIdFull {
		@SuppressWarnings("unused")
		public MainOrderIdFull() {

		}

		public MainOrderIdFull(int iMainOrderId, int iOrderId) {
			this.iMainOrderId = iMainOrderId;
			this.iOrderId = iOrderId;
		}

		private int iMainOrderId;
		private int iOrderId;

		public int getiMainOrderId() {
			return iMainOrderId;
		}

		public void setiMainOrderId(int iMainOrderId) {
			this.iMainOrderId = iMainOrderId;
		}

		public int getiOrderId() {
			return iOrderId;
		}

		public void setiOrderId(int iOrderId) {
			this.iOrderId = iOrderId;
		}

	}
	
	
	public Sp_GetSellerOrdersByOrderIds_v1 execute() {
		DefaultWapper db = new DefaultWapper();
		StringBuilder sqlCmd = new StringBuilder();
		DataUtil.SQL.declareVarOfSp("i", sqlCmd, "sellerId", sellerId);
		DataUtil.SQL.declareVarOfSp("i", sqlCmd, "orderType", orderType);
		DataUtil.SQL.declareVarOfSp("b", sqlCmd, "shangou", DataUtil.booltoInt(shangou));
		DataUtil.SQL.declareVarOfSp("dt", sqlCmd, "addBeginTime", addBeginTime);
		DataUtil.SQL.declareVarOfSp("dt", sqlCmd, "addEndTime", addEndTime);
		DataUtil.SQL.declareVarOfSp("dt", sqlCmd, "paidBeginTime", paidBeginTime);
		DataUtil.SQL.declareVarOfSp("dt", sqlCmd, "paidEndTime", paidEndTime);
		DataUtil.SQL.declareVarOfSp("b", sqlCmd, "paidInFull", DataUtil.booltoInt(paidInFull));
		DataUtil.SQL.declareVarOfSp("i", sqlCmd, "rowFrom", rowFrom);
		DataUtil.SQL.declareVarOfSp("i", sqlCmd, "rowTo", rowTo);
		DataUtil.SQL.declareVarOfSp("nv", sqlCmd, "buyerNickName", buyerNickName);
		DataUtil.SQL.declareVarOfSp("nv", sqlCmd, "productName", productName);
		DataUtil.SQL.declareVarOfSp("b", sqlCmd, "salesRefundOrderOnly", DataUtil.booltoInt(salesRefundOrderOnly));
		DataUtil.SQL.declareVarOfSp("b", sqlCmd, "domesticDelivered", DataUtil.booltoInt(domesticDelivered));
		DataUtil.SQL.declareVarOfSp("i", sqlCmd, "mainOrderId", mainOrderId);
		DataUtil.SQL.declareVarOfSp("i", sqlCmd, "OrderId", OrderId);
		DataUtil.SQL.declareVarOfSp("i", sqlCmd, "RemarkLevel", RemarkLevel);
		
		sqlCmd.append(" set nocount on;");
//		sqlCmd.append(" set @ProductName = isnull(@ProductName,'');");
		sqlCmd.append(" create table #mainOrderIdFull ([iMainOrderId] int not null, [iOrderId] int not null);");
		sqlCmd.append(" declare @sqlCmd nvarchar(max) ;");
		String s1 = " [order].[iMainOrderId], [order].[iOrderId] from [dbo].[Ymt_Orders] as [order] with(nolock'+ case "
		+" when @addBeginTime is not null or  @addEndTime is not null then ',index = idx_Ymt_Orders_iBuyerId_dAddTime'"
		+" when @paidBeginTime is not null or @paidEndTime is not null then ',index = idx_Ymt_Orders_iBuyerId_dPaidTime'"
		+" else '' end + ')'";
		//固定两表关联脚本开始
		if(!DataUtil.isNullOrEmpty(catalogStatus) || !DataUtil.Stringg.isNullOrEmpty(productName)){
			sqlCmd.append(" set @sqlCmd = '");
			sqlCmd.append(" select distinct "+s1);
			sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
			sqlCmd.append(" inner loop join [dbo].[Ymt_OrderInfo] as [orderInfo] with(nolock) on [orderInfo].[iOrderId] = [order].[iOrderId]'");
			
			//存在商品名称关键字查询
			sqlCmd.append(" if isnull(@ProductName,'') <> '' begin");
			sqlCmd.append(" set @sqlCmd = @sqlCmd + ' and [orderInfo].[sTitle] like ''%'' + @ProductName + ''%''' ;");
			sqlCmd.append(" end");
			
			//存在物流方式查询
			if(!DataUtil.isNullOrEmpty(catalogStatus)){
				sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
				sqlCmd.append(" and [orderInfo].[iCatalogStatus] in "+DataUtil.toInOfSql(catalogStatus).replaceAll("'", ""));
			}
		}else{
			sqlCmd.append(" set @sqlCmd = '");
			sqlCmd.append(" select "+s1);
		}
		
		//看是否进行了订单卖家备注关联查询
		sqlCmd.append(" if @RemarkLevel is not null begin");
		sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
		sqlCmd.append(" left join [dbo].[Ymt_O_OrderNote] as [orderNote] with(nolock) on [orderNote].[iOrderId] = [order].[iOrderId] and [orderNote].[iUserId] = [order].[iBuyerId]'");
		sqlCmd.append(" end");

		//固定参数
		sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
		sqlCmd.append(" where [order].[iBuyerId] = @sellerId'");
		
		//指定了订单卖家血备注查询条件:Ext记录不对等情况只能先关联结果再过滤
		sqlCmd.append(" if @RemarkLevel is not null begin");
		sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
		sqlCmd.append(" and [orderNote].[iRemarkLevel]' + case ");
		sqlCmd.append(" when @RemarkLevel = 0 then ' is null ' ");
		sqlCmd.append(" else ' = ' + cast(@RemarkLevel as varchar(8)) end;");
		sqlCmd.append(" end");
		
		//存在指定订单号查询
		sqlCmd.append(" if @OrderId is not null begin");
		sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
		sqlCmd.append(" and [order].[iOrderId] = ' + cast(@OrderId as varchar(36)) ;");
		sqlCmd.append(" end");
		
		//存在指定主订单号查询
		sqlCmd.append(" if @MainOrderId is not null begin");
		sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
		sqlCmd.append(" and [order].[iMainOrderId] = ' + cast(@MainOrderId as varchar(36));");
		sqlCmd.append(" end");
		
		//下单开始时间
		sqlCmd.append(" if (@addBeginTime is not null) begin");
		sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
		sqlCmd.append(" and [order].[dAddTime] >= convert(varchar(10), @addBeginTime, 120)';");
		sqlCmd.append(" end");
		
		//下单结束时间
		sqlCmd.append(" if (@addEndTime is not null) begin");
		sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
		sqlCmd.append(" and [order].[dAddTime] < convert(varchar(10), dateadd(day,1,@addEndTime) , 120)';");
		sqlCmd.append(" end");
		
		//支付开始时间
		sqlCmd.append(" if (@paidBeginTime is not null) begin");
		sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
		sqlCmd.append(" and ( ([order].[dPostPaidTime] is null and [order].[dPaidTime] >= convert(varchar(10), @paidBeginTime, 120)) or [order].[dPostPaidTime] >= convert(varchar(10), @paidBeginTime, 120) )';");
		sqlCmd.append(" end");
		
		//支付结束时间
		sqlCmd.append(" if (@paidEndTime is not null) begin");
		sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
		sqlCmd.append(" and ( ([order].[dPostPaidTime] is null and [order].[dPaidTime] < convert(varchar(10), dateadd(day,1,@paidEndTime), 120)) or [order].[dPostPaidTime] < convert(varchar(10), dateadd(day,1,@paidEndTime), 120) )';");
		sqlCmd.append(" end");
		
		//买家名称
		sqlCmd.append(" if (@BuyerNickName is not null) begin");
		sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
		sqlCmd.append(" and [order].[sBuyerNickName] = @BuyerNickName';");
		sqlCmd.append(" end");
		
		if(!DataUtil.isNullOrEmpty(orderStatus)){
			sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
			sqlCmd.append(" and [order].[iTradingStatus] in "+DataUtil.toInOfSql(orderStatus).replaceAll("'", "")+"';");
		}
		
		//存在指定全款
//		sqlCmd.append(" if @paidInFull is not null begin");
//		sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
//		sqlCmd.append(" and [order].[bPaidInFull] = ' + cast(@paidInFull as char(1));");
//		sqlCmd.append(" end");
		
		sqlCmd.append(" if @domesticDelivered is not null begin");
		sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
		sqlCmd.append(" and isnull([order].[bDomesticDelivered], 0) = '+ cast(@domesticDelivered as char(1));");
		sqlCmd.append(" end");
		
		sqlCmd.append(" if @salesRefundOrderOnly <> 0 begin");
		sqlCmd.append(" set @sqlCmd = @sqlCmd + '");
		sqlCmd.append(" and [order].[iSalesRefundStatus] is not null ';");
		sqlCmd.append(" end");
		
		sqlCmd.append(" set @sqlCmd = '");
		sqlCmd.append(" ;with datatable as (");
		sqlCmd.append(" ' + @sqlCmd + ')");
		sqlCmd.append(" insert into #mainOrderIdFull([iMainOrderId], [iOrderId]) select [iMainOrderId], [iOrderId] from datatable ;'");
		sqlCmd.append(" exec sp_executesql @sqlCmd"
				+" , N'@sellerId int,@buyerNickName nvarchar(20),@productName nvarchar(500),@addBeginTime datetime,@addEndTime datetime,@paidBeginTime datetime,@paidEndTime datetime'"
				+" , @SellerId,@buyerNickName,@productName,@addBeginTime,@addEndTime,@paidBeginTime,@paidEndTime ");
		
		
		//分页数据:	获取当前页的所有订单ID
		sqlCmd.append("; declare @PaginationOrderId table([iMainOrderId] int not null, [iOrderId] int not null);");
		sqlCmd.append(" insert into @PaginationOrderId( [iMainOrderId], [iOrderId] )");
		sqlCmd.append(" select [iMainOrderId],[iOrderId] from #mainOrderIdFull order by [iMainOrderId] desc");
		sqlCmd.append(" offset @rowFrom - 1 rows fetch next @rowTo - @rowFrom + 1 rows only");
		
		sqlCmd.append(" declare @maxMainOrderId int");
		sqlCmd.append(" declare @minMainOrderId int");
		
		sqlCmd.append(" select @maxMainOrderId = max([iMainOrderId]) from @PaginationOrderId");
		sqlCmd.append(" select @minMainOrderId = min([iMainOrderId]) from @PaginationOrderId");
		
		//最大：当前页与统计来的数据不一致且主订单数大于1个，剔除 (排除第一页，即其实为1的情况)
		sqlCmd.append(" if  ( select count(1) from #mainOrderIdFull where [iMainOrderId] = @maxMainOrderId ) <> ( select count(1) from @PaginationOrderId where [iMainOrderId] = @maxMainOrderId )");
		sqlCmd.append(" and (select count(distinct [iMainOrderId]) from @PaginationOrderId ) > 1 and @rowFrom > 1");
		sqlCmd.append(" begin delete @PaginationOrderId where [iMainOrderId] = @maxMainOrderId end ");
		
		//最小：当前页与统计来的数据不一致，剔除，再追加所有
		sqlCmd.append(" if ( select count(1) from #mainOrderIdFull where [iMainOrderId] = @minMainOrderId ) <> ( select count(1) from @PaginationOrderId where [iMainOrderId] = @minMainOrderId )");
		sqlCmd.append(" begin");
		sqlCmd.append(" delete @PaginationOrderId where [iMainOrderId] = @minMainOrderId");
		sqlCmd.append(" insert into @PaginationOrderId( [iMainOrderId], [iOrderId] )");
		sqlCmd.append(" select [iMainOrderId], [iOrderId] from #mainOrderIdFull where [iMainOrderId] = @minMainOrderId");
		sqlCmd.append(" end");
		
		//获取当前分页的OrderId
		List<Integer> orderIds = null;
		List<Map> oids = db.executeSql(sqlCmd+" ; select [iOrderId] from @PaginationOrderId; drop table #mainOrderIdFull");
		db.executeSql(" set nocount off;");
		if(!DataUtil.isNullOrEmpty(oids)){
			orderIds = new ArrayList<Integer>();
			for(Map m:oids){
				orderIds.add((int)m.get("iOrderId"));
			}
		}
		
		int totalCount = 0;
		sqlCmd.append(" select totalCount = count(1) from #mainOrderIdFull;");
		totalCount = (int)db.executeSql(sqlCmd+"; drop table #mainOrderIdFull").get(0).get("totalCount");
		db.executeSql(" set nocount off;");
		
		
		Sp_GetSellerOrdersByOrderIds_v1 spV1 = new Sp_GetSellerOrdersByOrderIds_v1();
		spV1.setOrderdt(orderIds);
		spV1.setSellerId(sellerId);
		spV1.setTotalCount(totalCount);
		return spV1.execute();
	}
	

}
